/*
 * DBeaver - Universal Database Manager
 * Copyright (C) 2010-2017 Serge Rider (serge@jkiss.org)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jkiss.dbeaver.model.sql;

import org.eclipse.core.resources.IFile;
import org.eclipse.core.runtime.Platform;
import org.jkiss.code.NotNull;
import org.jkiss.code.Nullable;
import org.jkiss.dbeaver.DBException;
import org.jkiss.dbeaver.Log;
import org.jkiss.dbeaver.model.*;
import org.jkiss.dbeaver.model.data.*;
import org.jkiss.dbeaver.model.edit.DBEPersistAction;
import org.jkiss.dbeaver.model.exec.DBCLogicalOperator;
import org.jkiss.dbeaver.model.exec.DBCSession;
import org.jkiss.dbeaver.model.impl.sql.BasicSQLDialect;
import org.jkiss.dbeaver.model.runtime.DBRProgressMonitor;
import org.jkiss.dbeaver.model.runtime.VoidProgressMonitor;
import org.jkiss.dbeaver.model.sql.format.SQLFormatter;
import org.jkiss.dbeaver.model.sql.format.SQLFormatterConfiguration;
import org.jkiss.dbeaver.model.struct.*;
import org.jkiss.dbeaver.model.struct.rdb.DBSTableForeignKey;
import org.jkiss.dbeaver.model.struct.rdb.DBSTableForeignKeyColumn;
import org.jkiss.dbeaver.utils.ContentUtils;
import org.jkiss.dbeaver.utils.GeneralUtils;
import org.jkiss.utils.ArrayUtils;
import org.jkiss.utils.CommonUtils;
import org.jkiss.utils.Pair;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.StringWriter;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL Utils
 */
public final class SQLUtils {

    private static final Log log = Log.getLog(SQLUtils.class);

    private static final Pattern PATTERN_OUT_PARAM = Pattern.compile("((\\?)|(:[a-z0-9]+))\\s*:=");
    private static final Pattern CREATE_PREFIX_PATTERN = Pattern.compile("(CREATE (:OR REPLACE)?).+", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);

    private static final int MIN_SQL_DESCRIPTION_LENGTH = 512;
    private static final int MAX_SQL_DESCRIPTION_LENGTH = 500;

    private static final String DBEAVER_DDL_COMMENT = "-- DDL generated by ";
    private static final String DBEAVER_DDL_WARNING = "-- WARNING: It may differ from actual native database DDL";
    private static final String DBEAVER_SCRIPT_DELIMITER = "$$";

    public static String stripTransformations(String query)
    {
        return query;
//        if (!query.contains(TOKEN_TRANSFORM_START)) {
//            return query;
//        } else {
//            return PATTERN_XFORM.matcher(query).replaceAll("");
//        }
    }

    public static String stripComments(@NotNull SQLDialect dialect, @NotNull String query)
    {
        Pair<String, String> multiLineComments = dialect.getMultiLineComments();
        return stripComments(
            query,
            multiLineComments == null ? null : multiLineComments.getFirst(),
            multiLineComments == null ? null : multiLineComments.getSecond(),
            dialect.getSingleLineComments());
    }

    public static boolean isCommentLine(SQLDialect dialect, String line) {
        for (String slc : dialect.getSingleLineComments()) {
            if (line.startsWith(slc)) {
                return true;
            }
        }
        return false;
    }

    public static String stripComments(@NotNull String query, @Nullable String mlCommentStart, @Nullable String mlCommentEnd, String[] slComments)
    {
        String leading = "", trailing = "";
        {
            int startPos, endPos;
            for (startPos = 0; startPos < query.length(); startPos++) {
                if (!Character.isWhitespace(query.charAt(startPos))) {
                    break;
                }
            }
            for (endPos = query.length() - 1; endPos > startPos; endPos--) {
                if (!Character.isWhitespace(query.charAt(endPos))) {
                    break;
                }
            }
            if (startPos > 0) {
                leading = query.substring(0, startPos);
            }
            if (endPos < query.length() - 1) {
                trailing = query.substring(endPos + 1);
            }
        }
        query = query.trim();
        if (mlCommentStart != null && mlCommentEnd != null && query.startsWith(mlCommentStart)) {
            int endPos = query.indexOf(mlCommentEnd);
            if (endPos != -1) {
                query = query.substring(endPos + mlCommentEnd.length());
            }
        }
        for (int i = 0; i < slComments.length; i++) {
            while (query.startsWith(slComments[i])) {
                int crPos = query.indexOf('\n');
                if (crPos == -1) {
                    // Query is comment line - return empty
                    query = "";
                    break;
                } else {
                    query = query.substring(crPos).trim();
                }
            }
        }
        return leading + query + trailing;
    }

    public static List<String> splitFilter(String filter)
    {
        if (CommonUtils.isEmpty(filter)) {
            return Collections.emptyList();
        }
        return CommonUtils.splitString(filter, ',');
    }

    public static boolean matchesAnyLike(String string, Collection<String> likes)
    {
        for (String like : likes) {
            if (matchesLike(string, like)) {
                return true;
            }
        }
        return false;
    }

    public static boolean isLikePattern(String like)
    {
        return like.indexOf('%') != -1 || like.indexOf('*') != -1 || like.indexOf('?') != -1;// || like.indexOf('_') != -1;
    }

    public static String makeLikePattern(String like)
    {
        StringBuilder result = new StringBuilder();
        for (int i = 0; i < like.length(); i++) {
            char c = like.charAt(i);
            if (c == '*') result.append(".*");
            else if (c == '?') result.append(".");
            else if (c == '%') result.append(".*");
            else if (Character.isLetterOrDigit(c)) result.append(c);
            else result.append("\\").append(c);
        }
        return result.toString();
    }

    public static String makeSQLLike(String like)
    {
        return like.replace("*", "%");
    }

    public static boolean matchesLike(String string, String like)
    {
        Pattern pattern = Pattern.compile(makeLikePattern(like), Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
        return pattern.matcher(string).matches();
    }

    public static void appendValue(StringBuilder buffer, DBSTypedObject type, Object value)
    {
        if (type.getDataKind() == DBPDataKind.NUMERIC || type.getDataKind() == DBPDataKind.BOOLEAN) {
            buffer.append(value);
        } else {
            buffer.append('\'').append(value).append('\'');
        }
    }

    public static boolean isStringQuoted(String string)
    {
        return string.length() > 1 && string.startsWith("'") && string.endsWith("'");
    }

    public static String quoteString(DBSObject object, String string)
    {
        return quoteString(object.getDataSource(), string);
    }

    public static String quoteString(DBPDataSource dataSource, String string)
    {
        return "'" + escapeString(dataSource, string) + "'";
    }

    public static String escapeString(DBPDataSource dataSource, String string)
    {
        @NotNull
        SQLDialect dialect = dataSource instanceof SQLDataSource ?
            ((SQLDataSource) dataSource).getSQLDialect() : BasicSQLDialect.INSTANCE;
        return dialect.escapeString(string);
    }

    public static String unQuoteString(DBPDataSource dataSource, String string)
    {
        if (string.length() > 1 && string.charAt(0) == '\'' && string.charAt(string.length() - 1) == '\'') {
            @NotNull
            SQLDialect dialect = dataSource instanceof SQLDataSource ?
                    ((SQLDataSource) dataSource).getSQLDialect() : BasicSQLDialect.INSTANCE;
            return dialect.unEscapeString(string.substring(1, string.length() - 1));
        }
        return string;
    }

    public static String getFirstKeyword(SQLDialect dialect, String query)
    {
        query = stripComments(dialect, query);
        int startPos = 0, endPos = -1;
        for (int i = 0; i < query.length(); i++) {
            if (Character.isLetterOrDigit(query.charAt(i))) {
                startPos = i;
                break;
            }
        }
        for (int i = startPos; i < query.length(); i++) {
            if (Character.isWhitespace(query.charAt(i))) {
                endPos = i;
                break;
            }
        }
        if (endPos == -1) {
            return query;
        }
        return query.substring(startPos, endPos);
    }

    @Nullable
    public static String getQueryOutputParameter(DBCSession session, String query)
    {
        final Matcher matcher = PATTERN_OUT_PARAM.matcher(query);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return null;
    }

    /**
     * Removes \\r characters from query.
     * Actually this is done specially for Oracle due to some bug in it's driver
     *
     *
     * @param dataSource
     * @param query query
     * @return normalized query
     */
    public static String makeUnifiedLineFeeds(DBPDataSource dataSource, String query)
    {
        SQLDialect dialect = SQLUtils.getDialectFromDataSource(dataSource);
        if (!dialect.isCRLFBroken()) {
            return query;
        }
        if (query.indexOf('\r') == -1) {
            return query;
        }
        StringBuilder result = new StringBuilder(query.length());
        for (int i = 0; i < query.length(); i++) {
            char c = query.charAt(i);
            if (c == '\r') {
                continue;
            }
            result.append(c);
        }
        return result.toString();
    }

    public static String formatSQL(SQLDataSource dataSource, String query)
    {
        SQLSyntaxManager syntaxManager = new SQLSyntaxManager();
        syntaxManager.init(dataSource.getSQLDialect(), dataSource.getContainer().getPreferenceStore());
        SQLFormatterConfiguration configuration = new SQLFormatterConfiguration(syntaxManager);
        SQLFormatter formatter = dataSource.getDataSource().getContainer().getPlatform().getSQLFormatterRegistry().createFormatter(configuration);
        if (formatter == null) {
            return query;
        }
        return formatter.format(query, configuration);
    }

    public static void appendLikeCondition(StringBuilder sql, String value, boolean not)
    {
        value = makeSQLLike(value);
        if (value.contains("%") || value.contains("_")) {
            if (not) sql.append(" NOT");
            sql.append(" LIKE ?");
        }  else {
            sql.append(not ? "<>?": "=?");
        }
    }

    public static boolean appendFirstClause(StringBuilder sql, boolean firstClause)
    {
        if (firstClause) {
            sql.append(" WHERE ");
        } else {
            sql.append(" AND ");
        }
        return false;
    }

    public static String trimQueryStatement(SQLSyntaxManager syntaxManager, String sql, boolean trimDelimiter)
    {
        if (sql.isEmpty() || !trimDelimiter) {
            // Do not trim delimiter
            return sql;
        }

        // Here we cur trailing query delimiter. most of DBs don't expect it in the end of query
        // However Oracle REQUIRES that block queries (e.g. DDL like CREATE PROCEDURE) must have trailing delimiter
        // So we check whether this query is a block query (by checking for all SQL dialect block delimiters)
        String trailingSpaces = "";
        {
            int trailingSpacesCount = 0;
            for (int i = sql.length() - 1; i >= 0; i--) {
                if (!Character.isWhitespace(sql.charAt(i))) {
                    break;
                }
                trailingSpacesCount++;
            }
            if (trailingSpacesCount > 0) {
                trailingSpaces = sql.substring(sql.length() - trailingSpacesCount);
                sql = sql.substring(0, sql.length() - trailingSpacesCount);
            }
        }
        for (String statementDelimiter : syntaxManager.getStatementDelimiters()) {
            if (!sql.endsWith(statementDelimiter) && sql.length() > statementDelimiter.length()) {
                continue;
            }
            if (Character.isAlphabetic(statementDelimiter.charAt(0))) {
                // Delimiter is alphabetic (e.g. "GO") so it must be prefixed with whitespace
                char lastChar = sql.charAt(sql.length() - statementDelimiter.length() - 1);
                if (Character.isUnicodeIdentifierPart(lastChar)) {
                    break;
                }
            }
            // Remove trailing delimiter only if it is not block end
            boolean isBlockQuery = false;
            String trimmed = sql.substring(0, sql.length() - statementDelimiter.length());
            {
                String test = trimmed.toUpperCase().trim();
                String[][] blockBoundStrings = syntaxManager.getDialect().getBlockBoundStrings();
                if (blockBoundStrings != null) {
                    for (String[] blocks : blockBoundStrings) {
                        int endIndex = test.indexOf(blocks[1]);
                        if (endIndex > 0) {
                            // This is a block query if it ends with 'END' or with 'END id'
                            if (test.endsWith(blocks[1])) {
                                isBlockQuery = true;
                                break;
                            } else {
                                String afterEnd = test.substring(endIndex + blocks[1].length()).trim();
                                if (CommonUtils.isJavaIdentifier(afterEnd)) {
                                    isBlockQuery = true;
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            if (!isBlockQuery) {
                sql = trimmed;
            }
            break;
        }
        return sql + trailingSpaces;
    }

    @NotNull
    public static SQLDialect getDialectFromObject(DBPObject object)
    {
        if (object instanceof DBSObject) {
            DBPDataSource dataSource = ((DBSObject)object).getDataSource();
            return getDialectFromDataSource(dataSource);
        }
        return BasicSQLDialect.INSTANCE;
    }

    @NotNull
    public static SQLDialect getDialectFromDataSource(DBPDataSource dataSource) {
        if (dataSource instanceof SQLDataSource) {
            return ((SQLDataSource) dataSource).getSQLDialect();
        }
        return BasicSQLDialect.INSTANCE;
    }

    public static void appendConditionString(
        @NotNull DBDDataFilter filter,
        @NotNull DBPDataSource dataSource,
        @Nullable String conditionTable,
        @NotNull StringBuilder query,
        boolean inlineCriteria)
    {
        String operator = filter.isAnyConstraint() ? " OR " : " AND ";  //$NON-NLS-1$ $NON-NLS-2$
        boolean hasWhere = false;
        for (DBDAttributeConstraint constraint : filter.getConstraints()) {
            String condition = getConstraintCondition(dataSource, constraint, inlineCriteria);
            if (condition == null) {
                continue;
            }

            if (hasWhere) query.append(operator);
            hasWhere = true;
            if (constraint.getEntityAlias() != null) {
                query.append(constraint.getEntityAlias()).append('.');
            } else if (conditionTable != null) {
                query.append(conditionTable).append('.');
            }
            // Attribute name could be an expression. So check if this is a real attribute
            // and generate full/quoted name for it.
            String attrName;
            DBSAttributeBase cAttr = constraint.getAttribute();
            if (cAttr instanceof DBDAttributeBinding) {
                DBDAttributeBinding binding = (DBDAttributeBinding) cAttr;
                if (binding.getEntityAttribute() != null &&
                    binding.getEntityAttribute().getName().equals(binding.getMetaAttribute().getName()))
                {
                    attrName = DBUtils.getObjectFullName(dataSource, binding, DBPEvaluationContext.DML);
                } else {
                    // Most likely it is an expression so we don't want to quote it
                    attrName = binding.getMetaAttribute().getName();
                }
            } else {
                attrName = DBUtils.getObjectFullName(dataSource, cAttr, DBPEvaluationContext.DML);
            }
            query.append(attrName).append(' ').append(condition);
        }

        if (!CommonUtils.isEmpty(filter.getWhere())) {
            if (hasWhere) query.append(operator);
            query.append(filter.getWhere());
        }
    }

    public static void appendOrderString(@NotNull DBDDataFilter filter, @NotNull DBPDataSource dataSource, @Nullable String conditionTable, @NotNull StringBuilder query)
    {
        // Construct ORDER BY
        boolean hasOrder = false;
        for (DBDAttributeConstraint co : filter.getOrderConstraints()) {
            if (hasOrder) query.append(',');
            if (conditionTable != null) {
                query.append(conditionTable).append('.');
            }
            query.append(DBUtils.getObjectFullName(co.getAttribute(), DBPEvaluationContext.DML));
            if (co.isOrderDescending()) {
                query.append(" DESC"); //$NON-NLS-1$
            }
            hasOrder = true;
        }
        if (!CommonUtils.isEmpty(filter.getOrder())) {
            if (hasOrder) query.append(',');
            query.append(filter.getOrder());
        }
    }

    @Nullable
    public static String getConstraintCondition(@NotNull DBPDataSource dataSource, @NotNull DBDAttributeConstraint constraint, boolean inlineCriteria) {
        String criteria = constraint.getCriteria();
        if (!CommonUtils.isEmpty(criteria)) {
            final char firstChar = criteria.trim().charAt(0);
            if (!Character.isLetter(firstChar) && firstChar != '=' && firstChar != '>' && firstChar != '<' && firstChar != '!') {
                return '=' + criteria;
            } else {
                return criteria;
            }
        } else if (constraint.getOperator() != null) {
            DBCLogicalOperator operator = constraint.getOperator();
            StringBuilder conString = new StringBuilder();
            Object value = constraint.getValue();
            if (DBUtils.isNullValue(value)) {
                if (operator.getArgumentCount() == 0) {
                    return operator.getStringValue();
                }
                conString.append("IS ");
                if (constraint.isReverseOperator()) {
                    conString.append("NOT ");
                }
                conString.append("NULL");
                return conString.toString();
            }
            if (constraint.isReverseOperator()) {
                conString.append("NOT ");
            }
            if (operator.getArgumentCount() > 0) {
                conString.append(operator.getStringValue());
                for (int i = 0; i < operator.getArgumentCount(); i++) {
                    if (i > 0) {
                        conString.append(" AND");
                    }
                    if (inlineCriteria) {
                        conString.append(' ').append(convertValueToSQL(dataSource, constraint.getAttribute(), value));
                    } else {
                        conString.append(" ?");
                    }
                }
            } else if (operator.getArgumentCount() < 0) {
                // Multiple arguments
                int valueCount = Array.getLength(value);
                boolean hasNull = false, hasNotNull = false;
                for (int i = 0; i < valueCount; i++) {
                    final boolean isNull = DBUtils.isNullValue(Array.get(value, i));
                    if (isNull && !hasNull) {
                        hasNull = true;
                    }
                    if (!isNull && !hasNotNull) {
                        hasNotNull = true;
                    }
                }
                if (!hasNotNull) {
                    return "IS NULL";
                }
                if (hasNull) {
                    conString.append("IS NULL OR ").append(DBUtils.getObjectFullName(dataSource, constraint.getAttribute(), DBPEvaluationContext.DML)).append(" ");
                }

                conString.append(operator.getStringValue());
                conString.append(" (");
                if (!value.getClass().isArray()) {
                    value = new Object[] {value};
                }
                boolean hasValue = false;
                for (int i = 0; i < valueCount; i++) {
                    Object itemValue = Array.get(value, i);
                    if (DBUtils.isNullValue(itemValue)) {
                        continue;
                    }
                    if (hasValue) {
                        conString.append(",");
                    }
                    hasValue = true;
                    if (inlineCriteria) {
                        conString.append(convertValueToSQL(dataSource, constraint.getAttribute(), itemValue));
                    } else {
                        conString.append("?");
                    }
                }
                conString.append(")");
            }
            return conString.toString();
        } else {
            return null;
        }
    }

    public static String convertValueToSQL(@NotNull DBPDataSource dataSource, @NotNull DBSAttributeBase attribute, @Nullable Object value) {
        DBDValueHandler valueHandler = DBUtils.findValueHandler(dataSource, attribute);

        return convertValueToSQL(dataSource, attribute, valueHandler, value);
    }

    public static String convertValueToSQL(@NotNull DBPDataSource dataSource, @NotNull DBSAttributeBase attribute, @NotNull DBDValueHandler valueHandler, @Nullable Object value) {
        if (DBUtils.isNullValue(value)) {
            return SQLConstants.NULL_VALUE;
        }

        String strValue;

        if (value instanceof DBDContent && dataSource instanceof SQLDataSource) {
            strValue = convertStreamToSQL(attribute, (DBDContent) value, valueHandler, (SQLDataSource) dataSource);
        } else {
            strValue = valueHandler.getValueDisplayString(attribute, value, DBDDisplayFormat.NATIVE);
        }
        if (value instanceof Number) {
            return strValue;
        }
        SQLDialect sqlDialect = null;
        if (dataSource instanceof SQLDataSource) {
            sqlDialect = ((SQLDataSource) dataSource).getSQLDialect();
        }
        switch (attribute.getDataKind()) {
            case BOOLEAN:
            case NUMERIC:
                return strValue;
            case CONTENT:
                return strValue;
            case STRING:
            case ROWID:
                if (sqlDialect != null) {
                    strValue = sqlDialect.escapeString(strValue);
                }
                return '\'' + strValue + '\'';
            default:
                if (sqlDialect != null) {
                    return sqlDialect.escapeScriptValue(attribute, value, strValue);
                }
                return strValue;
        }
    }

    public static String convertStreamToSQL(DBSAttributeBase attribute, DBDContent content, DBDValueHandler valueHandler, SQLDataSource dataSource) {
        try {
            DBRProgressMonitor monitor = new VoidProgressMonitor();
            if (valueHandler instanceof DBDContentValueHandler) {
                StringWriter buffer = new StringWriter();
                ((DBDContentValueHandler) valueHandler).writeStreamValue(monitor, dataSource, attribute, content, buffer);
                return buffer.toString();
            } else {
                if (ContentUtils.isTextContent(content)) {
                    String strValue = ContentUtils.getContentStringValue(monitor, content);
                    strValue = dataSource.getSQLDialect().escapeString(strValue);
                    return "'" + strValue + "'";
                } else {
                    byte[] binValue = ContentUtils.getContentBinaryValue(monitor, content);
                    return dataSource.getSQLDialect().getNativeBinaryFormatter().toString(binValue, 0, binValue.length);
                }
            }
        }
        catch (Throwable e) {
            log.warn(e);
            return SQLConstants.NULL_VALUE;
        }
    }

    public static String getColumnTypeModifiers(@Nullable DBPDataSource dataSource, DBSTypedObject column, @NotNull String typeName, @NotNull DBPDataKind dataKind) {
        if (column == null) {
            return null;
        }
        if (!(dataSource instanceof SQLDataSource)) {
            return null;
        }
        SQLDialect dialect = ((SQLDataSource) dataSource).getSQLDialect();
        return dialect.getColumnTypeModifiers(column, typeName, dataKind);
    }

    public static boolean isExecQuery(@NotNull SQLDialect dialect, String query) {
        // Check for EXEC query
        final String[] executeKeywords = dialect.getExecuteKeywords();
        if (executeKeywords.length > 0) {
            final String queryStart = getFirstKeyword(dialect, query);
            for (String keyword : executeKeywords) {
                if (keyword.equalsIgnoreCase(queryStart)) {
                    return true;
                }
            }
        }
        return false;
    }

    public static String getScriptDescripion(@NotNull String sql) {
        sql = stripComments(BasicSQLDialect.INSTANCE, sql);
        Matcher matcher = CREATE_PREFIX_PATTERN.matcher(sql);
        if (matcher.find() && matcher.start(0) == 0) {
            sql = sql.substring(matcher.end(1));
        }
        sql = sql.replaceAll(" +", " ");
        if (sql.length() > MAX_SQL_DESCRIPTION_LENGTH) {
            sql = sql.substring(0, MAX_SQL_DESCRIPTION_LENGTH) + " ...";
        }
        return sql;
    }

    @Nullable
    public static String getScriptDescription(@NotNull IFile sqlScript)
    {
        try {
            //log.debug("Read script '" + sqlScript.getName() + "' description");
            StringBuilder sql = new StringBuilder();
            try (BufferedReader is = new BufferedReader(new InputStreamReader(sqlScript.getContents()))) {
                for (;;) {
                    String line = is.readLine();
                    if (line == null) {
                        break;
                    }
                    line = line.trim();
                    if (line.startsWith(SQLConstants.SL_COMMENT) ||
                        line.startsWith("Rem") ||
                        line.startsWith("rem") ||
                        line.startsWith("REM")
                        )
                    {
                        continue;
                    }
                    sql.append(line).append('\n');
                    if (sql.length() > MIN_SQL_DESCRIPTION_LENGTH) {
                        break;
                    }
                }
            }
            return SQLUtils.getScriptDescripion(sql.toString());
        } catch (Exception e) {
            log.warn("", e);
        }
        return null;
    }

    @NotNull
    public static String generateCommentLine(DBPDataSource dataSource, String comment)
    {
        String slComment = SQLConstants.ML_COMMENT_END;
        if (dataSource instanceof SQLDataSource) {
            String[] slComments = ((SQLDataSource) dataSource).getSQLDialect().getSingleLineComments();
            if (!ArrayUtils.isEmpty(slComments)) {
                slComment = slComments[0];
            }
        }
        return slComment + " " + comment + GeneralUtils.getDefaultLineSeparator();
    }

    public static String generateParamList(int paramCount) {
        if (paramCount == 0) {
            return "";
        } else if (paramCount == 1) {
            return "?";
        }
        StringBuilder sql = new StringBuilder("?");
        for (int i = 0; i < paramCount - 1; i++) {
            sql.append(",?");
        }
        return sql.toString();
    }

    /**
     * Replaces single \r linefeeds with \n (some databases don't like them)
     */
    public static String fixLineFeeds(String sql) {
        if (sql.indexOf('\r') == -1) {
            return sql;
        }
        boolean hasFixes = false;
        char[] fixed = sql.toCharArray();
        for (int i = 0; i < fixed.length; i++) {
            if (fixed[i] == '\r' && (i == fixed.length - 1 || fixed[i + 1] != '\n')) {
                fixed[i] = '\n';
                hasFixes = true;
            }
        }
        return hasFixes ? String.valueOf(fixed) : sql;
    }

    /**
     * Compares two string ignoring extra whitespaces.
     * We can remove double whitespaces and any whitespaces between special chars (*-+,: etc).
     */
    public static boolean compareAliases(String str1, String str2) {
        return removeExtraSpaces(str1).equals(removeExtraSpaces(str2));
    }

    public static String removeExtraSpaces(String str) {
        if (str.indexOf(' ') == -1) {
            return str;
        }
        StringBuilder result = new StringBuilder(str.length());
        int length = str.length();
        for (int i = 0; i < length; i++) {
            char c = str.charAt(i);
            if (Character.isWhitespace(c)) {
                boolean needsSpace = i > 0 && Character.isLetterOrDigit(str.charAt(i - 1));
                for (i = i + 1; i < length; i++) {
                    c = str.charAt(i);
                    if (Character.isWhitespace(c)) {
                        continue;
                    }
                    if (needsSpace && Character.isLetterOrDigit(c)) {
                        // We need exactly one space before letter/digit
                        result.append(' ');
                    } else {
                        // We don't need spaces before control symbols
                    }
                    result.append(c);
                    break;
                }
            } else {
                result.append(c);
            }
        }
        return result.toString();
    }

    @NotNull
    public static String generateScript(DBPDataSource dataSource, DBEPersistAction[] persistActions, boolean addComments)
    {
        final SQLDialect sqlDialect = SQLUtils.getDialectFromDataSource(dataSource);
        final String lineSeparator = GeneralUtils.getDefaultLineSeparator();

        StringBuilder script = new StringBuilder(64);
        if (addComments) {
            script.append(DBEAVER_DDL_COMMENT).append(Platform.getProduct().getName()).append(lineSeparator)
                .append(DBEAVER_DDL_WARNING).append(lineSeparator);
        }
        if (persistActions != null) {
            String redefiner = sqlDialect.getScriptDelimiterRedefiner();
            for (DBEPersistAction action : persistActions) {
                String scriptLine = action.getScript();
                if (CommonUtils.isEmpty(scriptLine)) {
                    continue;
                }

                String delimiter = sqlDialect.getScriptDelimiter();
                if (action.isComplex() && redefiner != null) {
                    script.append(lineSeparator).append(redefiner).append(" ").append(DBEAVER_SCRIPT_DELIMITER).append(lineSeparator);
                    delimiter = DBEAVER_SCRIPT_DELIMITER;
                    script.append(delimiter).append(lineSeparator);
                } else if (action.getType() == DBEPersistAction.ActionType.COMMENT) {
                    if (script.length() > 2) {
                        int lfCount = 0;
                        for (int i = script.length() - 1; i >= 0; i--) {
                            if (!Character.isWhitespace(script.charAt(i))) {
                                break;
                            }
                            if (script.charAt(i) == '\n') lfCount++;
                        }
                        if (lfCount < 2) {
                            // Add line feed if we do not have empty line before
                            script.append(lineSeparator);
                        }
                    }
                }
                script.append(scriptLine);
                if (action.getType() != DBEPersistAction.ActionType.COMMENT) {
                    char lastChar = scriptLine.charAt(scriptLine.length() - 1);
                    if (!Character.isWhitespace(lastChar) && !Character.isLetterOrDigit(lastChar)) {
                        script.append(" ");
                    }
                    script.append(delimiter);
                } else {
                    script.append(lineSeparator);
                }
                script.append(lineSeparator);

                if (action.isComplex() && redefiner != null) {
                    script.append(redefiner).append(" ").append(sqlDialect.getScriptDelimiter()).append(lineSeparator);
                }
            }
        }
        return script.toString();
    }

    public static String[] splitFullIdentifier(final String fullName, char nameSeparator, String[][] quoteStrings) {
        return splitFullIdentifier(fullName, String.valueOf(nameSeparator), quoteStrings, false);
    }

    public static String[] splitFullIdentifier(final String fullName, String nameSeparator, String[][] quoteStrings, boolean keepQuotes) {
        String name = fullName.trim();
        if (ArrayUtils.isEmpty(quoteStrings)) {
            return name.split(Pattern.quote(nameSeparator));
        }
        if (!name.contains(nameSeparator)) {
            return new String[] { name };
        }
        List<String> nameList = new ArrayList<>();
        while (!name.isEmpty()) {
            boolean hadQuotedPart = false;
            for (String[] quotePair : quoteStrings) {
                String startQuote = quotePair[0];
                String endQuote = quotePair[1];
                if (!CommonUtils.isEmpty(startQuote) && !CommonUtils.isEmpty(endQuote) && name.startsWith(startQuote)) {
                    int endPos = name.indexOf(endQuote, startQuote.length());
                    if (endPos != -1) {
                        // Quoted part
                        String partName = keepQuotes ?
                            name.substring(0, endPos + endQuote.length()) :
                            name.substring(startQuote.length(), endPos);
                        nameList.add(partName);
                        name = name.substring(endPos + endQuote.length()).trim();
                        hadQuotedPart = true;
                        break;
                    }
                }
            }
            if (!hadQuotedPart) {
                int endPos = name.indexOf(nameSeparator);
                if (endPos != -1) {
                    nameList.add(name.substring(0, endPos));
                    name = name.substring(endPos);
                } else {
                    nameList.add(name);
                    break;
                }
            }
            if (!name.isEmpty() && name.startsWith(nameSeparator)) {
                name = name.substring(nameSeparator.length()).trim();
            }
        }
        return nameList.toArray(new String[nameList.size()]);
    }

    public static String generateTableJoin(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        // Try to find FK in left table referencing to right table
        String sql = generateTableJoinByAssociation(monitor, leftTable, leftAlias, rightTable, rightAlias);
        if (sql != null) return sql;

        // Now try right to left
        sql = generateTableJoinByAssociation(monitor, rightTable, rightAlias, leftTable, leftAlias);
        if (sql != null) return sql;

        // Try to find columns in left table which match unique key in right table
        sql = generateTableJoinByColumns(monitor, leftTable, leftAlias, rightTable, rightAlias);
        if (sql != null) return sql;

        // In reverse order
        sql = generateTableJoinByColumns(monitor, rightTable, rightAlias, leftTable, leftAlias);
        if (sql != null) return sql;

        return null;
    }

    private static String generateTableJoinByColumns(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        List<DBSEntityAttribute> leftIdentifier = new ArrayList<>(DBUtils.getBestTableIdentifier(monitor, leftTable));
        if (!leftIdentifier.isEmpty()) {
            List<DBSEntityAttribute> rightAttributes = new ArrayList<>();
            for (DBSEntityAttribute attr : leftIdentifier) {
                DBSEntityAttribute rightAttr = rightTable.getAttribute(monitor, attr.getName());
                if (rightAttr == null) {
                    break;
                }
                rightAttributes.add(rightAttr);
            }
            if (leftIdentifier.size() != rightAttributes.size()) {
                return null;
            }
            StringBuilder joinSQL = new StringBuilder();
            for (int i = 0; i < leftIdentifier.size(); i++) {
                joinSQL
                    .append(leftAlias).append(".").append(DBUtils.getQuotedIdentifier(leftIdentifier.get(i))).append(" = ")
                    .append(rightAlias).append(".").append(DBUtils.getQuotedIdentifier(rightAttributes.get(i)));
            }
            return joinSQL.toString();
        }
        return null;
    }

    private static String generateTableJoinByAssociation(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        Collection<? extends DBSEntityAssociation> associations = leftTable.getAssociations(monitor);
        if (!CommonUtils.isEmpty(associations)) {
            for (DBSEntityAssociation fk : associations) {
                if (fk instanceof DBSTableForeignKey && fk.getAssociatedEntity() == rightTable) {
                    return generateTablesJoin(monitor, (DBSTableForeignKey)fk, leftAlias, rightAlias);
                }
            }
        }
        return null;
    }

    private static String generateTablesJoin(DBRProgressMonitor monitor, DBSTableForeignKey fk, String leftAlias, String rightAlias) throws DBException {
        boolean hasCriteria = false;
        StringBuilder joinSQL = new StringBuilder();
        for (DBSEntityAttributeRef ar : fk.getAttributeReferences(monitor)) {
            if (ar instanceof DBSTableForeignKeyColumn) {
                if (hasCriteria) {
                    joinSQL.append(" AND ");
                }
                DBSTableForeignKeyColumn fkc = (DBSTableForeignKeyColumn)ar;
                joinSQL
                    .append(leftAlias).append(".").append(DBUtils.getQuotedIdentifier(fkc)).append(" = ")
                    .append(rightAlias).append(".").append(DBUtils.getQuotedIdentifier(fkc.getReferencedColumn()));
                hasCriteria = true;
            }
        }
        return joinSQL.toString();
    }

    public static String getTableAlias(DBSEntity table) {
        return CommonUtils.escapeIdentifier(table.getName());
    }

    public static void appendQueryConditions(DBPDataSource dataSource, @NotNull StringBuilder query, @Nullable String tableAlias, @Nullable DBDDataFilter dataFilter)
    {
        if (dataFilter != null && dataFilter.hasConditions()) {
            query.append("\nWHERE "); //$NON-NLS-1$
            appendConditionString(dataFilter, dataSource, tableAlias, query, true);
        }
    }

    public static void appendQueryOrder(DBPDataSource dataSource, @NotNull StringBuilder query, @Nullable String tableAlias, @Nullable DBDDataFilter dataFilter)
    {
        if (dataFilter != null) {
            // Construct ORDER BY
            if (dataFilter.hasOrdering()) {
                query.append("\nORDER BY "); //$NON-NLS-1$
                appendOrderString(dataFilter, dataSource, tableAlias, query);
            }
        }
    }
}